﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Web;

namespace PAS.Helper
{
    public class DataConnHelper
    {
        public static string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                                                AppDomain.CurrentDomain.BaseDirectory + "Apply.mdb";

        public static bool IsLogin
        {
            get
            {
                object obj = HttpContext.Current.Session["IsLogin"];
                return (obj != null && Convert.ToBoolean(obj));
            }
            set { HttpContext.Current.Session["IsLogin"] = value; }
        }

        public DataTable GetDataTable(string sql)
        {
            var result = new DataTable();
            var da = new OleDbDataAdapter(sql, ConnectionString);
            try
            {
                da.Fill(result);
            }
            finally
            {
                da.Dispose();
                da = null;
            }
            return result;
        }

        public int ExecSqlCommand(string commSql)
        {
            int reuslt = 0;
            var conn = new OleDbConnection(ConnectionString);
            var comm = new OleDbCommand();
            conn.Open();
            comm.Transaction = conn.BeginTransaction();
            try
            {
                comm.CommandText = commSql;
                comm.CommandType = CommandType.Text;
                comm.Connection = conn;
                comm.CommandTimeout = 900;
                reuslt = comm.ExecuteNonQuery();
                comm.Transaction.Commit();
            }
            catch(Exception ex)
            {
                comm.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                comm.Dispose();
                conn.Close();
            }
            return reuslt;
        }

        public Hashtable GetHashtableByNames(string dbTableName, string dbKeyColumnName, string orderByString)
        {
            var result = new Hashtable();
            string sql = "Select * From " + dbTableName + orderByString;
            DataTable dt = GetDataTable(sql);
            for (int i = 0; dt != null && dt.Rows.Count > 0 && i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strKeyVal = Convert.ToString(dr[dbKeyColumnName]);
                if (!result.Contains(strKeyVal))
                    result.Add(strKeyVal, dr);
            }
            return result;
        }

        public bool CheckSysAdminLogin(string pwd)
        {
            DataTable dt = GetDataTable("Select UserCode From UserInfo Where UserCode='sysAdmin' and Pwd='" + pwd + "'");
            return (dt != null && dt.Rows.Count > 0);
        }

        public string[] CheckPeopleLogin(string peopleName)
        {
            string[] reslut = null;
            DataTable dt = GetDataTable("Select Id,Name,GroupName From PeopleInfo Where Name='" + peopleName + "'");
            if(dt != null && dt.Rows.Count > 0)
            {
                reslut = new string[3];
                string strId = Convert.ToString(dt.Rows[0][0]);
                string strName = Convert.ToString(dt.Rows[0][1]);
                string strGroupName = Convert.ToString(dt.Rows[0][2]);
                reslut[0] = strId;
                reslut[1] = strName;
                reslut[2] = strGroupName;
            }
            return reslut;
        }

        public DataTable GetOptionList()
        {
            DataTable dt =
                GetDataTable("Select Id," + OptionInfo.DbName + "," + OptionInfo.DbGroupName + " From " +
                             OptionInfo.DbTableName + " Order By "+OptionInfo.DbGroupName+","+ OptionInfo.DbName);
            return dt;
        }
        public StringCollection GetPeopleOptionSelectItemIds(int peopleId)
        {
            StringCollection sc = new StringCollection();

            DataTable dt =
                GetDataTable("Select OptionId from People_Option po Where po.PeopleId="+peopleId);
            if(dt!= null && dt.Rows.Count > 0)
            {
                foreach(DataRow dr in dt.Rows)
                {
                    sc.Add(Convert.ToString(dr[0]));
                }
            }
            return sc;
        }

        public bool UpdatePeopleInfo(string strName,string strGroupName,string strId)
        {
            int amount = ExecSqlCommand("Update "+PeopleInfo.DbTableName+" Set ["+PeopleInfo.DbName+"]='"+strName
                +"' , ["+PeopleInfo.DbGroupName+"]='"+strGroupName+"' Where [Id]=" + strId);
            return amount > 0;
        }

        public bool AddPeopleInfo(string strName, string strGroupName)
        {
            DataTable dt = GetDataTable("Select Count(*) From "+PeopleInfo.DbTableName+" Where ["+PeopleInfo.DbName+"]='"+strName+"'");
            if (dt != null && dt.Rows.Count > 0)
                throw new ArgumentException(PeopleInfo.MemoName+"["+strName+"]已经存在不能添加");
            else
            {
                int amount = ExecSqlCommand("Insert into "+PeopleInfo.DbTableName+" (["+PeopleInfo.DbName+"],["
                    +PeopleInfo.DbGroupName+"]) values('"+strName+"','"+strGroupName+"')");
                return amount > 0;
            }
        }

        public bool DeletePeopleInfo( string strId)
        {
            int amount = ExecSqlCommand("Delete From " + PeopleInfo.DbTableName + " Where [Id]=" + strId);
            return amount > 0;
        }

        public bool UpdateOptionInfo(string strName, string strGroupName, string strId)
        {
            int amount = ExecSqlCommand("Update " + OptionInfo.DbTableName + " Set [" + OptionInfo.DbName + "]='" + strName
                + "' , [" + OptionInfo.DbGroupName + "]='" + strGroupName + "' Where [Id]=" + strId);
            return amount > 0;
        }

        public bool AddOptionInfo(string strName, string strGroupName)
        {
            DataTable dt = GetDataTable("Select Count(*) From " + OptionInfo.DbTableName + " Where [" + OptionInfo.DbName + "]='" + strName + "'");
            if (dt != null && dt.Rows.Count > 0)
                throw new ArgumentException(OptionInfo.MemoName + "[" + strName + "]已经存在不能添加");
            else
            {
                int amount = ExecSqlCommand("Insert into " + OptionInfo.DbTableName + " ([" + OptionInfo.DbName + "],["
                    + OptionInfo.DbGroupName + "]) values('" + strName + "','" + strGroupName + "')");
                return amount > 0;
            }
        }

        public bool DeleteOptionInfoInfo(string strId)
        {
            int amount = ExecSqlCommand("Delete From " + OptionInfo.DbTableName + " Where [Id]=" + strId);
            return amount > 0;
        }
    }
}